USE [DSRSystem_Temp]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spPrepareSnapshotData]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spPrepareSnapshotData]
GO

CREATE PROCEDURE [dbo].[spPrepareSnapshotData]
@ChangedSince datetime
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @transaction_isolation_level SMALLINT;

	SELECT @transaction_isolation_level = transaction_isolation_level
    FROM sys.dm_exec_requests
    WHERE session_id = @@spid;

    print('initial transaction_isolation_level: ' + STR(@transaction_isolation_level))

	SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
	BEGIN TRANSACTION;

	SELECT @transaction_isolation_level = transaction_isolation_level
    FROM sys.dm_exec_requests
    WHERE session_id = @@spid;

	print('after SET TRANSACTION ISOLATION LEVEL SNAPSHOT' + STR(@transaction_isolation_level))

	EXECUTE [dbo].[spPrepareTables] 

	-- execute preparations for corporate data sync cstep
	declare @startCD datetime;
	select @startCd = getdate();	
	EXEC  [dbo].[spCDPrepareSnapshotData] @ChangedSince
	print ('[dbo].[spCDPrepareSnapshotData] duraton: ' + str(datediff(second, getdate(), @startCd)) )


	declare @totalTime datetime
	declare @startTime datetime
	declare @endTime datetime
	declare @elapsedTime int
	declare @rowsAffected int

	select @totalTime = getdate()
	select @startTime = getdate()

	declare @DateTimeMinValue datetime = '1753-01-01'
	declare @LastUpdate datetime
	declare @LastSyncDate datetime
	declare @SyncAll bit = 1


	DECLARE @CompanyIds nvarchar(max)
	DECLARE @XML XML


	DECLARE @Log TABLE
	(
		Description nvarchar(max) NOT NULL,
		ElapsedTime int NOT NULL,
		RowsAffected int NOT NULL
	)

	TRUNCATE TABLE FinalizedCompanies
	INSERT INTO FinalizedCompanies
	EXECUTE [DSRSystem].[dbo].[spGetFinalizedCompanies]
	
	set @rowsAffected = @@ROWCOUNT

	select @endTime = getdate()
	select @elapsedTime = datediff(second, @startTime, @endTime)
	insert into @Log (Description, ElapsedTime, RowsAffected)
	values ('Extract Finalized Companies', @elapsedTime, @rowsAffected)
	select @startTime = getdate()


	SELECT TOP(1) @LastUpdate = LastUpdate FROM [dbo].[SyncLog] ORDER BY LastSyncDate DESC

	
	if (@ChangedSince = @DateTimeMinValue) set @LastUpdate = null


	DECLARE @SelectedCompaniesCount int
	DECLARE @SelectedCompanies TABLE
	(
		Id uniqueidentifier
	)


	IF @LastUpdate IS NULL 
		BEGIN
			INSERT INTO @SelectedCompanies
			SELECT CompanyId AS Id FROM FinalizedCompanies

			set @SelectedCompaniesCount = @@ROWCOUNT

			insert into @Log (Description, ElapsedTime, RowsAffected)
			values ('SQL Log: ' + str(@SelectedCompaniesCount) + ' finalized companies changed since the beginning of time', 0, @SelectedCompaniesCount)
		END
	ELSE
		BEGIN
			INSERT INTO @SelectedCompanies
			SELECT CompanyId AS Id FROM FinalizedCompanies 
			WHERE LastFinalUpdate > @LastUpdate
			OR LastCentralizedResearchUpdate > @LastUpdate
			OR LastCorporateDataUpdate > @LastUpdate

			set @SelectedCompaniesCount = @@ROWCOUNT

			insert into @Log (Description, ElapsedTime, RowsAffected)
			values ('SQL Log: ' + str(@SelectedCompaniesCount) + ' finalized companies changed since ' +  CONVERT(nvarchar(30), @LastUpdate, 126), 0, @SelectedCompaniesCount)
		END



	if (@SelectedCompaniesCount) > 0
	begin

		SET @XML = (SELECT * FROM @SelectedCompanies FOR XML RAW (''), ROOT('CompanyIds'), ELEMENTS, TYPE)
		SET @CompanyIds = CONVERT(NVARCHAR(MAX), @XML, 1)

		
		TRUNCATE TABLE [dbo].[IndicatorTemplates]
		INSERT INTO [dbo].[IndicatorTemplates]
		EXECUTE [DSRSystem].[dbo].[spGetIndicatorTemplates]
		
		set @rowsAffected = @@ROWCOUNT

		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('SQL Log: Extract Indicator Templates', @elapsedTime, @rowsAffected)
		select @startTime = getdate()


		--=== Indicators ================================================
		
		DELETE Indicators FROM dbo.Indicators AS Indicators
		LEFT OUTER JOIN dbo.FinalizedCompanies AS FinalizedCompanies ON Indicators.CompanyId = FinalizedCompanies.CompanyId
		WHERE  (FinalizedCompanies.CompanyId IS NULL)   

		set @rowsAffected = @@ROWCOUNT
		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('SQL Log: Delete indicators that do not belong to current companies', @elapsedTime, @rowsAffected)
		select @startTime = getdate()


		IF (@LastUpdate IS NULL) BEGIN
			TRUNCATE TABLE [dbo].[Indicators] END 
		ELSE BEGIN
			DELETE Indicators FROM dbo.Indicators AS Indicators
			INNER JOIN @SelectedCompanies AS SelectedCompanies ON Indicators.CompanyId = SelectedCompanies.Id
		END

		set @rowsAffected = @@ROWCOUNT
		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('SQL Log: Delete indicators that are going to be updated', @elapsedTime, @rowsAffected)
		select @startTime = getdate()


		INSERT INTO [dbo].[Indicators]
		EXECUTE [DSRSystem].[dbo].[spGetStandardIndicatorByCompanyIds] @CompanyIds

		set @rowsAffected = @@ROWCOUNT
		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('SQL Log: Indicators have been updated', @elapsedTime, @rowsAffected)
		select @startTime = getdate()


		--=== Indicators Comments ================================================

		DELETE IndicatorComments FROM dbo.IndicatorComments AS IndicatorComments
		LEFT OUTER JOIN dbo.FinalizedCompanies AS FinalizedCompanies ON IndicatorComments.CompanyId = FinalizedCompanies.CompanyId
		WHERE  (FinalizedCompanies.CompanyId IS NULL)   

		set @rowsAffected = @@ROWCOUNT
		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('SQL Log: Delete indicator comments that do not belong to current companies', @elapsedTime, @rowsAffected)
		select @startTime = getdate()


		IF (@LastUpdate IS NULL) BEGIN
			TRUNCATE TABLE [dbo].[IndicatorComments] END 
		ELSE BEGIN
			DELETE IndicatorComments FROM dbo.IndicatorComments AS IndicatorComments
			INNER JOIN @SelectedCompanies AS SelectedCompanies ON IndicatorComments.CompanyId = SelectedCompanies.Id
		END

		set @rowsAffected = @@ROWCOUNT
		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('SQL Log: Delete indicator comments that are going to be updated', @elapsedTime, @rowsAffected)
		select @startTime = getdate()


		INSERT INTO [dbo].[IndicatorComments]
		EXECUTE [DSRSystem].[dbo].[spGetIndicatorCommentsByCompanyIds] @CompanyIds

		set @rowsAffected = @@ROWCOUNT
		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('SQL Log: Indicator comments have been updated', @elapsedTime, @rowsAffected)
		select @startTime = getdate()

		--=== Indicator DataTables ================================================


		EXEC [dbo].[spUpdateIndicatorDataTables] @CompanyIds, @LastUpdate


		--=== Indicator Tickboxes ================================================
 
		DELETE IndicatorTickboxes FROM dbo.IndicatorTickboxes AS IndicatorTickboxes
		LEFT OUTER JOIN dbo.FinalizedCompanies AS FinalizedCompanies ON IndicatorTickboxes.CompanyId = FinalizedCompanies.CompanyId
		WHERE  (FinalizedCompanies.CompanyId IS NULL)   

		set @rowsAffected = @@ROWCOUNT
		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('SQL Log: Delete indicator tickboxes that do not belong to current companies', @elapsedTime, @rowsAffected)
		select @startTime = getdate()


		IF (@LastUpdate IS NULL) BEGIN
			TRUNCATE TABLE [dbo].[IndicatorTickboxes] END 
		ELSE BEGIN
			DELETE IndicatorTickboxes FROM dbo.IndicatorTickboxes AS IndicatorTickboxes
			INNER JOIN @SelectedCompanies AS SelectedCompanies ON IndicatorTickboxes.CompanyId = SelectedCompanies.Id
		END

		set @rowsAffected = @@ROWCOUNT
		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('SQL Log: Delete indicator tickboxes that are going to be updated', @elapsedTime, @rowsAffected)
		select @startTime = getdate()


		INSERT INTO [dbo].[IndicatorTickboxes]
		EXECUTE [DSRSystem].[dbo].[spGetIndicatorTickboxesByCompanyIds] @CompanyIds

		set @rowsAffected = @@ROWCOUNT
		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('SQL Log: Indicator tickboxes have been updated', @elapsedTime, @rowsAffected)
		select @startTime = getdate()


		--=== Sources ================================================

		TRUNCATE TABLE [dbo].[Sources]
		INSERT INTO [dbo].[Sources]
		EXECUTE [DSRSystem].[dbo].[GetNonControversySources]

		
		set @rowsAffected = @@ROWCOUNT
		select @endTime = getdate()
		select @elapsedTime = datediff(second, @startTime, @endTime)
		insert into @Log (Description, ElapsedTime, RowsAffected)
		values ('SQL Log: Sources have been updated', @elapsedTime, @rowsAffected)
		select @startTime = getdate()

	end

	declare @LastCorporateDataUpdate datetime
	declare @LastFinalUpdate datetime
	declare @LastCentralizedResearchUpdate datetime

	select @LastFinalUpdate = max(LastFinalUpdate) from [dbo].[FinalizedCompanies]
	select @LastCentralizedResearchUpdate = max(LastCentralizedResearchUpdate) from [dbo].[FinalizedCompanies]
	select @LastCorporateDataUpdate = max(LastCorporateDataUpdate) from [dbo].[FinalizedCompanies]

	set @LastUpdate = @LastFinalUpdate
	if @LastCorporateDataUpdate > @LastUpdate set @LastUpdate = @LastCorporateDataUpdate
	if @LastCentralizedResearchUpdate > @LastUpdate set @LastUpdate = @LastCentralizedResearchUpdate

	insert into [dbo].[SyncLog] ([LastSyncDate], [ElapsedTime], [LastUpdate])
	values (GETUTCDATE(), @elapsedTime, @LastUpdate)


	select @endTime = getdate()
	select @elapsedTime = datediff(second, @totalTime, @endTime)
	print 'SnapshotData completed successfully => Total elapsed time:' +  str(@elapsedTime)
	insert into @Log (Description, ElapsedTime, RowsAffected)
	values ('SnapshotData completed successfully', @elapsedTime, 0)

	COMMIT TRANSACTION

	select * from @Log

END

GO


